Как ускорить 1С – Анализ запросов с помощью SQL Profiler

Другие статьи по оптимизации 1С:

«Как ускорить 1С – Многопоточная обработка данных»

«Как ускорить 1С за 5 минут – Протокол Shared Memory»

«3 главных вопроса про временные таблицы 1С»

Анализ запросов с помощью SQL Profiler

Что Вы узнаете из этой статьи?

  • Предназначение инструмента трассировки SQL Profiler
  • Как отследить текст запроса к СУБД, в который транслируется запрос 1С
  • Настройки фильтров трассировки
  • Как выполнить персональную настройку SQL Profiler

Зачастую в работе возникает ситуация, когда запрос в 1С по каким-то причинам работает медленно, но анализ текста запроса не говорит нам о каких-либо проблемах.

В таком случае приходится изучать эту проблему на более низком уровне. Для этого нам нужно посмотреть текcт SQL-запроса и план запроса. Для этого можно использовать SQL Profiler.

SQL Profiler – предназначение

SQL Profiler – это программа, входящая в MS SQL Server, которая предназначена для просмотра всех событий, которые происходят в SQL-сервере. Иначе говоря, она нужна для записи трассировки.

В каких случаях данный инструмент может быть полезен 1С программисту? Прежде всего, можно получить текст запроса на языке SQL и посмотреть его план. Это также можно сделать и в технологическом журнале (ТЖ), но план запроса в ТЖ получается не таким удобным и требует наличия некоторых навыков и умений. К тому же в профайлере можно посмотреть не только текстовый, но и графический план выполнения запроса, что является более удобным.

Также профайлер позволяет узнать:

  • запросы длиннее определенного времени
  • запросы к определенной таблице
  • ожидания на блокировках
  • таймауты
  • взаимоблокировки и т. д.

Анализ запросов с помощью SQL Profiler

Зачастую Profiler применяется именно для анализа запросов. И при этом нужно анализировать не все исполняемые запросы, а то, как определенный запрос на языке 1С транслируется в SQL, и обращать внимание на его план выполнения.

В частности, это бывает необходимо, чтобы понять, почему запрос выполняется медленно. Или при построении большого и сложного запроса необходимо удостовериться, что запрос на языке SQL не содержит соединений с подзапросом.

Для отслеживания запроса в трассировке выполняем следующие шаги:

1. Запускаем SQL Profiler: Пуск — Все программы — Microsoft SQL Server 2008 R2 — Средства обеспечения производительности — SQLProfiler.

2. Создаем новую трассировку: Файл – Создать трассировку (Ctrl+N).

3. Указываем сервер СУБД, на котором находится наша база данных и нажимаем Соединить:

Запрос у серверу

Нам ничто не мешает выполнять трассировку сервера СУБД, находящегося на любом другом компьютере.

4. В появившемся окне Свойства трассировки переключаемся на закладку Выбор событий:

свойства трассировки

5. Далее нужно указать события и их свойства, которые мы хотим видеть в трассировке.

Так как нам нужны запросы и планы запросов, то необходимо включить соответствующие события. Для показа полного списка свойств и событий включаем флаги Показать все события и Показать все столбцы. Теперь необходимо выбрать только события, приведенные на рисунке ниже, остальные же – требуется отключить:

Свойства и события в трассировке

Описание этих событий:

  • ShowplanStatisticsProfile– текстовый план выполнения запроса
  • ShowplanXMLStatisticsProfile– графический план выполнения запроса
  • RPC:Completed– текст запроса, если он выполняется как процедура (если выполняется запрос 1С с параметрами)
  • SQL:BatchCompleted– текст запроса, если он выполняется как обычный запрос (если выполнялся запрос 1С без параметров)

6. На этом этапе необходима настройка фильтра для выбранных событий. Если фильтр не установлен, то мы будем видеть запросы для всех БД, расположенных на данном сервере СУБД. По кнопке Фильтры столбцов устанавливаем фильтр по имени базы данных:

Настройка фильтров для событий

Теперь мы видим в трассировке только запросы к БД «TestBase_8_2».

Также можно поставить фильтр и по другим полям, наиболее интересные из них:

  • Duration (длительность)
  • TextData (обычно это текст запроса)
  • RowCounts (количество строк, возвращаемых запросом)

Допустим, нам необходимо «отловить» все запросы к таблице «_InfoRg4312» длительностью более 3-х секунд в базе данных «TestBase_8_2». Для этого необходимо:

a) Установить фильтр по базе данных (см. выше)
b) Установить фильтр по длительности (устанавливается в миллисекундах):

настройка фильтра SQLProfiler

c) Установить фильтр по тексту запроса:

image009

Для задания фильтра по тексту запроса используем маску. В случае необходимости отслеживать запросы, которые обращаются к нескольким таблицам, создается несколько элементов в разделе «Похоже на». Наложенные условия фильтров работают совместно.

7. Теперь запускаем трассировку с помощью кнопки Запустить в окне Свойства трассировки и наблюдаем события, попадающие под установленные фильтры, отображение которых было настроено.

Кнопки командной панели служат для управления трассировкой:

Управление трассировкой

Назначение кнопок:

  • Ластик – очищает окно трассировки
  • Пуск – запускает трассировку
  • Пауза – ставит трассировку на паузу, при нажатии на Пуск трассировка возобновляется
  • Стоп – останавливает трассировку

8. Окно трассировки состоит из двух частей. В верхней части находятся события и их свойства, в нижней – информация, зависящая от типа событий. Для нашего примера здесь будет отображаться либо текст запроса, либо его план.

9. Запустим на выполнение запрос в консоли запросов 1С и посмотрим, как он отразится в профайлере:

Выполнение запросов в консоли запросов 1С

Запрос 1С в SQLProfiler

По поведению трассировки видно, что запросов в итоге получилось несколько, и только один из них нам интересен. Остальные запросы – служебные.

10. Свойства событий дают возможность оценить:

  • сколько секунд выполнялся запрос (Duration)
  • сколько было логических чтений (Reads)
  • сколько строк запрос вернул в результате (RowCounts) и т.д.

В нашем случае запрос выполнялся 2 миллисекунды, сделал 4 логических чтения и вернул 1 строку.

11. Если взглянуть на одно событие выше, то можно увидеть план запроса в графическом виде:

План запроса в графическом виде

Из плана видно, что поиск осуществляется по индексу по цене, этот план нельзя назвать идеальным, так как индекс не является покрывающим, поля код и наименование получаются с помощью KeyLookup, что отнимает 50% времени.

Используя контекстное меню, полученный графический план запроса возможно сохранить в отдельный файл с расширением *.SQLPlan и открыть его в профайлере на другом компьютере или с помощью программы SQL Sentry Plan Explorer, которая является более продвинутой.

Сохранение плана запроса

12. Если подняться еще выше, то мы увидим тот же план запроса, но уже в текстовом виде. Именно этот план отображается в ТЖ, ЦУП и прочих средствах контроля производительности 1С.

План запроса в текстовом виде

13. Через меню Файл – Сохранить как можно сохранить всю трассировку в различные форматы:

  • В формат самого профайлера, то есть с расширением *.trc
  • В формат xml
  • Сделать из трассировки шаблон (См. следующий пункт)
  • Cохранить полученную трассировку в виде таблицы базы данных. Это весьма удобный способ, когда, к примеру, нужно найти самый медленный запрос в трассировке или отфильтровать запросы по какому-либо параметру.

Используем меню Файл – Сохранить как – Таблица трассировки – Выбираем сервер СУБД и подключаемся к нему.

Затем выбираем базу данных на указанном сервере, указываем имя таблицы, куда будет сохранена трассировка. Можно использовать существующую таблицу, или дать ей новое имя, и тогда эта таблица будет создана автоматически.

Сохранение трассировки в виде таблицы баз данных

Теперь возможно строить запросы любой сложности к нашей таблице: к примеру, искать наиболее долго выполняющиеся запросы.

Поиск долго выполняющихся запросов

Также нужно помнить, что Duration сохраняется в таблицу в миллионных долях секунды, и при выводе результата нужно переводить значение в миллисекунды. Также в таблице присутствует столбец RowNumber, показывающий номер данной строки в трассировке.

14. При частом использовании профайлера для анализа запросов постоянная настройка нужных событий и фильтров будет постоянно отнимать у вас много времени.

В данном случае нам помогут шаблоны трассировок, где мы настраиваем нужные нам фильтры и порядок колонок, а далее просто используем уже имеющийся шаблон при создании новой трассировки.

Для создания шаблона используем меню Файл – Шаблоны – Новый шаблон:

Создание шаблона трассировки

На первой закладке указываем тип сервера, имя шаблона и при необходимости ставим флаг для использования данного шаблона по умолчанию.

На второй закладке делаем выбор нужных событий и осуществляем настройку фильтров (как было показано выше).

Дополнительно рекомендуется выполнить настройку порядка столбцов в трассировке, что экономит время при последующем анализе запросов. Удобным представляется следующий порядок:

Настройка порядка столбцов для анализа запросов

При создании новой трассировки можем указать нужный шаблон, и тогда на второй закладке все фильтры и события заполнятся автоматически по созданному шаблону.

Шаблон трассировки

Бурмистров Андрей

PDF-версия статьи для участников группы ВКонтакте

Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.

Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.

Статья по оптимизации 1С - в PDF-формате

Статья в PDF-формате

Вы можете скачать эту статью в формате PDF по следующей ссылке: Ссылка доступна для зарегистрированных пользователей)

Ссылка доступна для зарегистрированных пользователей)
Ссылка доступна для зарегистрированных пользователей)
Ссылка доступна для зарегистрированных пользователей)

Если Вы уже участник группы – нужно просто повторно авторизоваться в ВКонтакте, чтобы скрипт Вас узнал. В случае проблем решение стандартное: очистить кеш браузера или подписаться через другой браузер.
Станьте экспертом по оптимизации 1С, изучив наш курс
«Ускорение и оптимизация систем на 1С:Предприятие 8.3 (2016). Подготовка на 1С:Эксперт по технологическим вопросам»

Содержание курса и форма заказа: https://курсы-по-1с.рф/1c-v8/optimization/

35 учебных часов, подготовка к 1С:Эксперт, правильная настройка серверной части, оптимизация кода, мониторинг загруженности оборудования и прочие взрослые вещи.

Комментарии / обсуждение (11):

  1. Татьяна

    Здравствуйте! Вы пишете: “По поведению трассировки видно, что запросов в итоге получилось несколько, и только один из них нам интересен. Остальные запросы – служебные.” А как узнать, какой из них наш основной запрос?

    • Андрей Бурмистров

      Татьяна, здравствуйте!
      Наш запрос будет обращаться к таблице которая соответствует объекту метаданных, а так же это понятно по набору выбираемых полей и полям по которым идет условие.
      Что бы узнать какая таблица СУБД какому объекту метаданных соответствует, можно использовать функцию ПолучитьСтруктуруХраненияБазыДанных.
      Вот пример одной из многих обработок с помощью которых можно посмотреть структуру хранения.

  2. Ярослав Бессчетнов

    Добрый день. Подскажите, пожалуйста, почему в Profiler отображается не название используемого индекса (_Reference7_ByFieldFld109), а его номер (_Reference7_9)?
    В примерах: Index Seek(OBJECT:([demo].[dbo].[_Reference7].[_Reference7_ByFieldFld109] AS [T1])
    У меня: Index Seek(OBJECT:([demo].[dbo].[_Reference7].[_Reference7_9] AS [T1])
    И только в обработке “Просмотр метаданных” я по номеру 9 нахожу этот индекс в списке и понимаю, что на самом деле поиск шел по индексу _Reference7_ByFieldFld109?

    • Андрей Бурмистров

      Здравствуйте!
      Начиная с одной из версий платформы, индексы в СУБД стали именоваться по другому принципу “ИмяТаблицы_ПорядковыйНомер”, ранее нумерация была связана с названием и типом полей из которых индекс состоит. Изменение в платформе произошло относительно недавно, с этим и связано такое отличие.

      • Ярослав Бессчетнов

        Андрей, правильно я понимаю, что порядковый номер индекса совпадает с порядком в обработке “Структура хранения метаданных” и начинается не с 0, а с 1?

    • Татьяна Гужавина

      Добрый день!
      Статья опубликована с согласия автора с небольшими изменениями.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Вход на сайт

Зарегистрироваться

Подтверждение регистрации будет отправлено на указанный e-mail.

Я подтверждаю, что ознакомлен(а) с Пользовательским соглашением, принимаю его условия и даю свое согласие на обработку моих персональных данных.

Восстановить доступ

E-mail или логин

Ссылка на создание нового пароля будет отправлена на указанный e-mail.